package com.soft.mainsite.common.db;

import com.soft.mainsite.common.db.impl.M;
import com.soft.mainsite.common.db.impl.MysqlDbTableAssistant;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import static com.soft.mainsite.common.db.impl.M.BIG_INT_TYPE;

/**
 * Description:很神奇的一个类 哈哈
 * 创建数据表和字段
 *
 * @author Jin
 * @create 2017-05-08
 */
@Service
public class DbTableAssistantService {


    private final static Logger log = LoggerFactory.getLogger(DbTableAssistantService.class);

    private final String PRIMARY_KEY = "content_id";
    private final String TABLE_PREFIX = "site_model_";

    @Autowired
    private MysqlDbTableAssistant mysql;

    @Autowired
    @Qualifier("masterDataSource")
    private DataSource dataSource;


    public void createDbtable(String tableName) throws SQLException {
        String sql = mysql.create().tableName(TABLE_PREFIX + tableName).initColumn(PRIMARY_KEY, BIG_INT_TYPE, 20, false, "0", true, true).builderSql();
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
    }

    public void deleteDbtable(String tableName) throws SQLException {
        log.info("create table  [{}] begin", tableName);

        String sql = mysql.delete().tableName(TABLE_PREFIX + tableName).builderSql();
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
    }

    public void addDbTableColumn(String tableName, String columnName, M columnType, Integer length, boolean autoIncrement, String defaultValue, boolean isNotNull, boolean isPrimaryKey) throws SQLException {
        log.info("create table [{}] column [{}] begin", tableName, columnName);
        String sql = mysql.edit().tableName(TABLE_PREFIX + tableName).addColumn(columnName, columnType, length, autoIncrement, defaultValue, isNotNull, isPrimaryKey).builderSql();
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
    }


    public void editDbTableColumn(String tableName, String columnName, String newColumnName, M columnType, Integer length, boolean autoIncrement, String defaultValue, boolean isNotNull) throws SQLException {
        log.info("create table [{}] column [{}] begin", tableName, columnName);
        String sql = mysql.edit().tableName(TABLE_PREFIX + tableName).changeColumn(columnName, newColumnName, columnType, length, autoIncrement, defaultValue, isNotNull).builderSql();
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
    }

    public void deleteDbTableColumn(String tableName, String columnName, boolean isPrimaryKey) throws SQLException {
        log.info("create table [{}]  column [{}] begin", tableName, columnName);

        String sql = mysql.edit().tableName(TABLE_PREFIX + tableName).dropColumn(columnName, isPrimaryKey).builderSql();
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
        log.info("created table [{}] column [{}] success", tableName, columnName);
    }

}
